package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.ScheduleInfo;
import c0907m.group2.aitsm.util.Helper;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Schedule table.
 */
public class ScheduleDb extends BaseDb {
    private String insert = "INSERT INTO schedule (subject_id, class_id, user_id, start_time, end_time) VALUES (?, ?, ?, ?, ?)";
    private String update = "UPDATE schedule SET subject_id = ?, class_id = ?, user_id = ?, start_time = ?, end_time = ? WHERE id = ?";

    /**
     * Insert a Schedule into database.
     *
     * @param schedule row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(ScheduleInfo schedule) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            buildStatement(schedule);
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a Schedule into database.
     *
     * @param schedule row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(ScheduleInfo schedule) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(schedule);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Schedules into database.
     *
     * @param schedules list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<ScheduleInfo> schedules) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (ScheduleInfo schedule : schedules) {
                doUpdate(schedule);
            }
        } finally {
            close();
        }
    }

    /**
     * Get a Schedule for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public ScheduleInfo select(int id) throws SQLException {
        String select = "SELECT * FROM schedule WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Schedules.
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<ScheduleInfo> select() throws SQLException {
        return select(0, 0, 0, null, null);
    }

    /**
     * Get a list of Schedules.
     *
     * @param subject_id the subject's ID to search. Zero for all.
     * @param class_id   the class's ID to search. Zero for all.
     * @param user_id    the user's ID to search. Zero for all.
     * @param from       from this date. Null for all.
     * @param to         to this date. Null for all.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<ScheduleInfo> select(int subject_id, int class_id, int user_id, java.util.Date from, java.util.Date to) throws SQLException {
        String select = "SELECT * FROM schedule WHERE 1 = 1";
        if (subject_id != 0) select += " AND subject_id = " + subject_id;
        if (class_id != 0) select += " AND class_id = " + class_id;
        if (user_id != 0) select += " AND user_id = " + user_id;
        if (from != null) select += " AND start_time >= '" + Helper.fromDateTime(from) + "'";
        if (to != null) select += " AND end_time <= '" + Helper.fromDateTime(to) + "'";
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<ScheduleInfo> list = new ArrayList<ScheduleInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param schedule the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(ScheduleInfo schedule) throws SQLException {
        buildStatement(schedule);
        st.setInt(6, schedule.getId());
        st.executeUpdate();
    }

    /**
     * Build the PreparedStatement from a data object.
     *
     * @param schedule the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void buildStatement(ScheduleInfo schedule) throws SQLException {
        st.setInt(1, schedule.getSubjectId());
        st.setInt(2, schedule.getClassId());
        st.setInt(3, schedule.getUserId());
        st.setTimestamp(4, new Timestamp(schedule.getStartTime().getTime()));
        st.setTimestamp(5, new Timestamp(schedule.getEndTime().getTime()));
    }

    /**
     * Build a data object from ResultSet.
     *
     * @return the data object.
     * @throws SQLException if a database access error occurs.
     */
    private ScheduleInfo getResult() throws SQLException {
        ScheduleInfo clas = new ScheduleInfo();
        clas.setId(rs.getInt(1));
        clas.setSubjectId(rs.getInt(2));
        clas.setClassId(rs.getInt(3));
        clas.setUserId(rs.getInt(4));
        clas.setStartTime(rs.getTimestamp(5));
        clas.setEndTime(rs.getTimestamp(6));

        return clas;
    }
}
